<?php
class NoteSystem
{
    const TAG_TABLE_COL = 'tags2.name';
    const TABLE = 'notes';
    
    //取得單筆資料
    public static function get($id)
    {
        $id = (int)$id;
        $sql = "SELECT * FROM ".self::TABLE." WHERE id={$id} LIMIT 1";
        $result = db_PDO::query(db_PDO::getConn(), $sql);
        $retval = array();
        db_PDO::eachRow(function($row) use(&$retval){
            $retval[] = $row;
        }, $result);
        return current($retval);
    }

    //查詢
    public static function find(array $tags=null)
    {
        $retval = array();
        db_PDO::eachRow(function($row) use(&$retval, $tags){
            $match_tags = array_intersect(TagsManager::parse($row['tags']), $tags);
            if (empty($tags) || ! empty($match_tags))
            {
                $retval[] = $row;
            }
        }, db_PDO::query(db_PDO::getConn(), db_PDO::buildSelect(array('*'), self::TABLE)));
        return $retval;
    }

    //新增
    public static function add(array $source)
    {
        $tags = TagsManager::parse($source['tags']);
        $gold = abs((int)$source['gold']);
        $subject = $source['subject']?:'無標題';
        $note = $source['note'];
        $sql_format = "INSERT INTO ".self::TABLE." (tags, gold, subject, note) VALUES ('%s', '%s', '%s', '%s')";
        $sql = sprintf($sql_format,
               join(',', $tags),
               $gold,
               $subject,
               $note
        );
        db_PDO::query(db_PDO::getConn(), $sql);
        $tag_ctrl = TagsManager::instance(self::TAG_TABLE_COL);
        $tag_ctrl->add($tags);
    }
    
    //編輯
    public static function edit($id, array $source)
    {
        $id = (int)$id;
        $tags = TagsManager::parse($source['tags']);
        $gold = abs((int)$source['gold']);
        $subject = $source['subject']?:'無標題';
        $note = $source['note'];
        $sql_format = "UPDATE ".self::TABLE." SET tags='%s', gold='%s', subject='%s', note='%s' WHERE id={$id}";
        $sql = sprintf($sql_format,
               join(',', $tags),
               $gold,
               $subject,
               $note
        );//var_dump($sql);
        db_PDO::query(db_PDO::getConn(), $sql);
        $tag_ctrl = TagsManager::instance(self::TAG_TABLE_COL);
        $tag_ctrl->add($tags);
    }
    
    //刪除
    public static function del($id)
    {
        $id = (int)$id;
        $sql = "DELETE FROM ".self::TABLE." WHERE id={$id}";
        db_PDO::query(db_PDO::getConn(), $sql);
    }
}